//=====================================================================
//
//  File:      BulkCopyFromVariables.cpp
//  Summary:   This sample shows how to do a bulk copy from program variables bound with bcp_bind; 
//             data sent with bcp_sendrow.
//
//             Assumes the following TSQL is run before each program invocation:
//             use AdventureWorks
//             DROP TABLE BCPSource
//             DROP TABLE BCPTarget 
//             CREATE TABLE BCPSource (cola int PRIMARY KEY, colb CHAR(10) NULL)
//             INSERT INTO BCPSource (cola, colb) VALUES (1, 'aaa')
//             INSERT INTO BCPSource (cola, colb) VALUES (2, 'bbb')
//             CREATE TABLE BCPTarget (cola int PRIMARY KEY, colb CHAR(10) NULL)
// 
//  Date:      December 23, 2005     
//
//---------------------------------------------------------------------
//
//  This file is part of the Microsoft SQL Server Code Samples.
//  Copyright (C) Microsoft Corporation.  All rights reserved.
//
//This source code is intended only as a supplement to Microsoft
//Development Tools and/or on-line documentation.  See these other
//materials for detailed information regarding Microsoft code samples.
//
//THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
//ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO 
//THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
//PARTICULAR PURPOSE.
//
//===================================================================== 

#include <stdio.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h>

SQLHENV henv = SQL_NULL_HENV;
HDBC hdbc1 = SQL_NULL_HDBC, hdbc2 = SQL_NULL_HDBC;
SQLHSTMT hstmt2 = SQL_NULL_HSTMT;

void Cleanup() {
   if (hstmt2 != SQL_NULL_HSTMT)
      SQLFreeHandle(SQL_HANDLE_STMT, hstmt2);

   if (hdbc1 != SQL_NULL_HDBC) {
      SQLDisconnect(hdbc1);
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
   }
 
   if (hdbc2 != SQL_NULL_HDBC) {
      SQLDisconnect(hdbc2);
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc2);
   }

   if (henv != SQL_NULL_HENV)
      SQLFreeHandle(SQL_HANDLE_ENV, henv);
}

int main() {
   RETCODE retcode;

   // BCP variables.
   char *terminator = "\0";

   // bcp_done takes a different format return code because it returns number of rows bulk copied
   // after the last bcp_batch call.
   DBINT cRowsDone = 0;

   // Set up separate return code for bcp_sendrow so it is not using the same retcode as SQLFetch.
   RETCODE SendRet;

   // Column variables.  cbCola and cbColb must be defined right before Cola and szColb because 
   // they are used as bulk copy indicator variables.
   struct ColaData {
      int cbCola;
      SQLINTEGER Cola;
   } ColaInst;

   struct ColbData {
      int cbColb;
      SQLCHAR szColb[11];
   } ColbInst;

   // Allocate the ODBC environment and save handle.
   retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
   if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
      printf("SQLAllocHandle(Env) Failed\n\n");
      Cleanup();
      return(9);
   }

   // Notify ODBC that this is an ODBC 3.0 app.
   retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
   if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
      printf("SQLSetEnvAttr(ODBC version) Failed\n\n");
      Cleanup();
      return(9);    
   }

   // Allocate ODBC connection handle, set bulk copy mode, and connect.
   retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
   if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
      printf("SQLAllocHandle(hdbc1) Failed\n\n");
      Cleanup();
      return(9);
   }

   retcode = SQLSetConnectAttr(hdbc1, SQL_COPT_SS_BCP, (void *)SQL_BCP_ON, SQL_IS_INTEGER);
   if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
      printf("SQLSetConnectAttr(hdbc1) Failed\n\n");
      Cleanup();
      return(9);
   }

   // sample uses Integrated Security, create the SQL Server DSN using Windows NT authentication
   retcode = SQLConnect(hdbc1, (UCHAR*)"AdventureWorks", SQL_NTS, (UCHAR*)"", SQL_NTS, (UCHAR*)"", SQL_NTS);
   if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
      printf("SQLConnect() Failed\n\n");
      Cleanup();
      return(9);
   }

   // Initialize the bulk copy.
   retcode = bcp_init(hdbc1, "AdventureWorks..BCPTarget", NULL, NULL, DB_IN);
   if ( (retcode != SUCCEED) ) {
      printf("bcp_init(hdbc1) Failed\n\n");
      Cleanup();
      return(9);
   }

   // Bind the program variables for the bulk copy.
   retcode = bcp_bind(hdbc1, (BYTE *)&ColaInst.cbCola, 4, SQL_VARLEN_DATA, NULL, (INT)NULL, SQLINT4, 1);
   if ( (retcode != SUCCEED) ) {
      printf("bcp_bind(hdbc1) Failed\n\n");
      Cleanup();
      return(9);
   }

   // Could normally use strlen to calculate the bcp_bind cbTerm parameter, but this terminator 
   // is a null byte (\0), which gives strlen a value of 0. Explicitly give cbTerm a value of 1.
   retcode = bcp_bind(hdbc1, (BYTE *)&ColbInst.cbColb, 4, 11, (UCHAR*)terminator, 1, SQLCHARACTER, 2);
   if ( (retcode != SUCCEED) ) {
      printf("bcp_bind(hdbc1) Failed\n\n");
      Cleanup();
      return(9);
   }

   // Allocate second ODBC connection handle so bulk copy and cursor operations do not conflict.
   retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc2);
   if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
      printf("SQLAllocHandle(hdbc2) Failed\n\n");
      Cleanup();
      return(9);
   }

   // Sample uses Integrated Security, create SQL Server DSN using Windows NT authentication. 
   retcode = SQLConnect(hdbc2, (UCHAR*)"AdventureWorks", SQL_NTS, (UCHAR*)"", SQL_NTS, (UCHAR*)"", SQL_NTS);
   if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
      printf("SQLConnect() Failed\n\n");
      Cleanup();
      return(9);
   }

   // Allocate ODBC statement handle.
   retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc2, &hstmt2);
   if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
      printf("SQLAllocHandle(hstmt2) Failed\n\n");
      Cleanup();
      return(9);
   }

	SQLLEN lDataLengthA;
	SQLLEN lDataLengthB;

   // Bind the SELECT statement to the same program variables bound to the bulk copy operation.
   retcode = SQLBindCol(hstmt2, 1, SQL_C_SLONG, &ColaInst.Cola, 0, &lDataLengthA);
   if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
      printf("SQLBindCol(hstmt2) Failed\n\n");
      Cleanup();
      return(9);
   }

   retcode = SQLBindCol(hstmt2, 2, SQL_C_CHAR, &ColbInst.szColb, 11, &lDataLengthB);
   if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
      printf("SQLBindCol(hstmt2) Failed\n\n");
      Cleanup();
      return(9);
   }

   // Execute SELECT statement to build a cursor containing data to be bulk copied to new table.
   retcode = SQLExecDirect(hstmt2, (UCHAR*)"SELECT * FROM BCPSource", SQL_NTS);
   if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
      printf("SQLExecDirect Failed\n\n");
      Cleanup();
      return(9);
   }
   // Go into a loop fetching rows from the cursor until each row is fetched. Because the 
   // bcp_bind calls and SQLBindCol calls each reference the same variables, each fetch fills 
   // the variables used by bcp_sendrow, so all you have to do to send the data to SQL Server is 
   // to call bcp_sendrow.
   while ( (retcode = SQLFetch(hstmt2) ) != SQL_NO_DATA) {
      if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
         printf("SQLFetch Failed\n\n");
         Cleanup();
         return(9);
      }

   	ColaInst.cbCola = (int)lDataLengthA;
	ColbInst.cbColb = (int)lDataLengthB;

	  if ( (SendRet = bcp_sendrow(hdbc1) ) != SUCCEED ) {
         printf("bcp_sendrow(hdbc1) Failed\n\n");
         Cleanup();
         return(9);
      }
   }

   // Signal the end of the bulk copy operation.
   cRowsDone = bcp_done(hdbc1);
   if ( (cRowsDone == -1) ) {
      printf("bcp_done(hdbc1) Failed\n\n");
      Cleanup();
      return(9);
   }

   printf("Number of rows bulk copied after last bcp_batch call = %d.\n", cRowsDone);

   // Cleanup.
   SQLFreeHandle(SQL_HANDLE_STMT, hstmt2);
   SQLDisconnect(hdbc1);
   SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
   SQLDisconnect(hdbc2);
   SQLFreeHandle(SQL_HANDLE_DBC, hdbc2);
   SQLFreeHandle(SQL_HANDLE_ENV, henv);
}